﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Text;
using NPOI.XWPF.UserModel; // for reading Word documents
using UglyToad.PdfPig; // for reading PDF documents
using UglyToad.PdfPig.Content; // for reading PDF content

public class IndexDatabase
{
    private SQLiteConnection dbConnection;

    public IndexDatabase(string dbPath)
    {
        dbConnection = new SQLiteConnection($"Data Source={dbPath};Version=3;");
        dbConnection.Open();
        InitializeDatabase();
    }

    public void CloseConnection()
    {
        dbConnection.Close();
    }

    private void InitializeDatabase()
    {
        string[] colNames = { "ID", "FILE_NAME", "FULL_PATH", "FILE_SIZE", "DATE_MODIFIED", "CONTENT" };
        string[] colTypes = { "INTEGER PRIMARY KEY AUTOINCREMENT", "TEXT", "TEXT", "INTEGER", "DATETIME", "TEXT" };
        CreateTable("FILES", colNames, colTypes);
    }

    private void CreateTable(string tableName, string[] colNames, string[] colTypes)
    {
        StringBuilder queryBuilder = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableName} (");
        for (int i = 0; i < colNames.Length; i++)
        {
            queryBuilder.Append($"{colNames[i]} {colTypes[i]}");
            if (i < colNames.Length - 1)
                queryBuilder.Append(", ");
        }
        queryBuilder.Append(")");
        ExecuteNonQuery(queryBuilder.ToString());
    }

    private void ExecuteNonQuery(string query)
    {
        using (var command = new SQLiteCommand(query, dbConnection))
        {
            command.ExecuteNonQuery();
        }
    }

    public void AddFileToIndex(string filePath, string content)
    {
        using (var command = new SQLiteCommand(dbConnection))
        {
            command.CommandText = "INSERT INTO FILES (FILE_NAME, FULL_PATH, FILE_SIZE, DATE_MODIFIED, CONTENT) VALUES (@fileName, @fullPath, @fileSize, @dateModified, @content)";
            command.Parameters.AddWithValue("@fileName", Path.GetFileName(filePath));
            command.Parameters.AddWithValue("@fullPath", filePath);
            command.Parameters.AddWithValue("@fileSize", new FileInfo(filePath).Length);
            command.Parameters.AddWithValue("@dateModified", File.GetLastWriteTime(filePath));
            command.Parameters.AddWithValue("@content", content);
            command.ExecuteNonQuery();
        }
    }

    public List<string> SearchFilesByContent(string keyword)
    {
        List<string> result = new List<string>();
        string query = "SELECT FULL_PATH FROM FILES WHERE CONTENT LIKE @keyword";

        using (var command = new SQLiteCommand(query, dbConnection))
        {
            command.Parameters.AddWithValue("@keyword", "%" + keyword + "%");
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    result.Add(reader["FULL_PATH"].ToString());
                }
            }
        }

        return result;
    }

    public string ExtractContent(string filePath)
    {
        string extension = Path.GetExtension(filePath).ToLower();
        switch (extension)
        {
            case ".docx":
                return GetTextFromWord(filePath);
            case ".pdf":
                return GetTextFromPDF(filePath);
            case ".txt":
                return GetTextFromTxt(filePath);
            default:
                return string.Empty;
        }
    }

    private string GetTextFromWord(string filePath)
    {
        StringBuilder text = new StringBuilder();
        try
        {
            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                XWPFDocument document = new XWPFDocument(fileStream);
                foreach (var paragraph in document.Paragraphs)
                {
                    text.Append(paragraph.Text);
                }
            }
        }
        catch (ICSharpCode.SharpZipLib.Zip.ZipException ex)
        {
            Console.WriteLine($"ZipException: {ex.Message}");
            // 处理文件格式错误或文件损坏问题
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Exception: {ex.Message}");
            // 处理其他类型的异常
        }
        return text.ToString();
    }

    private string GetTextFromPDF(string filePath)
    {
        StringBuilder text = new StringBuilder();
        using (PdfDocument document = PdfDocument.Open(filePath))
        {
            foreach (Page page in document.GetPages())
            {
                text.Append(page.Text);
            }
        }
        return text.ToString();
    }

    private string GetTextFromTxt(string filePath)
    {
        StringBuilder text = new StringBuilder();
        try
        {
            using (StreamReader reader = new StreamReader(filePath))
            {
                text.Append(reader.ReadToEnd());
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Exception: {ex.Message}");
        }
        return text.ToString();
    }
}
